# coding:utf-8

import os
import sys
import xlwt

BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
sys.path.extend([BASE_DIR, ])
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "finance_c2c.settings")

import django

django.setup()

from django.db import connection


def update_password():
    book = xlwt.Workbook()  # 创建excel对象
    # 写表头
    column = 0
    sheet1 = book.add_sheet('price')  # 添加一个表
    # 写表头
    header_line = ["省份名称", "数量", "价格", ]
    for header in header_line:
        sheet1.write(0, column, header)
        column += 1
    cursor = connection.cursor()
    # 查询
    row = 1
    for i in range(1, 181):
        price = i / 100
        sql = "select g.`provinceName`, COUNT(g.`id`) FROM `finance_c2c_new`.`czb_oilinfo` AS o JOIN  `finance_c2c_new`.`czb_gasstationinfo` as g on g.id = o.`gs_station_id`  where o.`oilNo` = 92 and convert((o.`priceGun` - o.`priceYfq`) ,decimal(3,2)) = %s GROUP BY  g.`provinceName` " % price
        print(sql)
        cursor.execute(sql)
        # 返回一行
        raw = cursor.fetchall()
        for one in raw:
            print(one[0], one[1])
            sheet1.write(row, 0, one[0])
            sheet1.write(row, 1, one[1])
            sheet1.write(row, 2, price)
            row += 1
        book.save("price.xls")


if __name__ == '__main__':
    update_password()
    pass
